package br.edu.ifpb.CaixaEletronico;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.PreparedStatement;

public class Pesquisa {
	private Connection connection;
	private int rs;
	private int rs1;
	private int rs2;
	private int rs3;
	private int rs4;
	private int rs5;
	private int rs6;

	  public Pesquisa() {
	    this.connection = new ConnectionFactory().getConnection();
	  }
	  
	  public boolean PesquisaConta(String cpf)throws SQLException{
		  boolean valor=false;
		  PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from tb_conta where CPF = '"+cpf+"'");
			
		  ResultSet rs = stmt.executeQuery();
		  if(rs.next()){
			  valor= true;
		  }
		  	stmt.close();
			rs.close();
			return valor;
		  
		  
		  
	  }
	  public boolean PesquisaCadastro(String cpf,String senha) throws SQLException{
		    
		  boolean valor=false;
		  PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select * from tb_clientes where CPF = '"+cpf+"'and SENHA = "+senha);
			
			ResultSet rs = stmt.executeQuery();
			
			if(rs.next()){
				valor= true;
			}
			stmt.close();
			rs.close();
			return valor;
	  }
	  
	  public double PesquisaSaldo(String cpf) throws SQLException{
		  PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select*from tb_conta where CPF = '"+cpf+"'");
			
			ResultSet rs = stmt.executeQuery();
			
			double valor=-1;
			
			if(rs.next()){
			
			valor = rs.getDouble("SALDO");
			
			}
		
			stmt.close();
			
			rs.close();
		  
			return  valor;
		  
	  }
	  
	  public int PesquisaSalario(String cpf) throws SQLException{
		 
		  PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select*from tb_clientes where CPF = '"+cpf+"';");
			
		  ResultSet rs = stmt.executeQuery();
		  
		  rs.next();
		   
		  int valor = rs.getInt("SALARIO");
		
		rs.close();
		stmt.close();
			
		  return  valor;
		  
		  
	  };
	  public void Saque(String cpf,double saldo,double saque)throws SQLException{
		  
		  double valor = saldo-saque;
		
		  PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("UPDATE tb_conta SET SALDO ="+valor+" WHERE CPF = '"+cpf+"';");
			
		  setRs(stmt.executeUpdate());
		  
		  
		  stmt.close();
		  
		  
		  Extrato extrato = new Extrato();
		  
		  extrato.adicionamovimetacao(cpf,"Saque",saque,0,valor);
		  
		  
		  return ;
	  }
	  
	  public void EfetuarExtrato(String cpf) throws SQLException{
		  
		  PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("Select *from tb_operacao WHERE CPF = '"+cpf+"';");
		  
		  ResultSet rs = stmt.executeQuery();
		   
		  while(rs.next()){
			  System.out.println("Dia : "+rs.getDate("DIA"));
			  System.out.println("Historico : "+rs.getString("HISTORICO"));
			  System.out.println("Retirada : "+rs.getDouble("DEBITO"));
			  System.out.println("Insercao : "+rs.getDouble("CREDITO"));
			  System.out.println("Saldo : "+rs.getDouble("SALDO")+"\n");
		  }
		  
		  rs.close();
		  stmt.close();
		  
	  }
	  public void Cedulas(double valor){
		  int cedula;
		  if(valor>100){
			  cedula=(int) (valor/100);
			  System.out.println("Notas de 100 : "+cedula);
			  valor=valor-cedula*100;   
		  } 
		  if(valor>=50){
			  System.out.println("Notas de 50 :"+1);
			  valor=valor-50; 
		  }  
		  if(valor>=20){
			  cedula=(int) (valor/20);
			  System.out.println("Notas de 20 : "+cedula);
			  valor=valor-20*cedula;
		  }
		  if(valor>=10){
			  System.out.println("Notas de 10 : "+1);
			  valor=valor-10;
		  }
		  if(valor>=5){
			  System.out.println("Notas de 5 : "+1);
			  valor=valor-5;
		  }
		  if(valor>=2){
			  cedula=(int)(valor/2);
			  System.out.println("Notas de 2 : "+cedula);
		  }
		  	System.out.println("\n");
	  }

	public int PesquisaValorEmprestimo(String cpf)throws SQLException {
		
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select EMPRESTIMO from tb_clientes where CPF = '"+cpf+"';");
		
		  ResultSet rs = stmt.executeQuery();
		  
		  rs.next();
		   
		  int valor = rs.getInt("EMPRESTIMO");
		
		  rs.close();
		  stmt.close();
			
		  return valor;
		
	}
	public void AtualizaSaldo(double valor,String cpf) throws SQLException{
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("UPDATE tb_conta SET SALDO = SALDO +"+valor+" WHERE CPF = "+cpf+";");
		
		 setRs3(stmt.executeUpdate());
		  
		  stmt.close();
	}
	public void AtualizaConta(String cpf,double valor) throws SQLException{
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("UPDATE tb_clientes SET EMPRESTIMO = EMPRESTIMO -"+valor+" WHERE CPF = "+cpf+";");
		
		  setRs2(stmt.executeUpdate());
		  
		  stmt.close();
	}
	public void AtualizaEmprestimo(int id,int parcelas)throws SQLException{
		
		  PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("UPDATE tb_emprestimo SET PARCELAS = PARCELAS -"+parcelas+" WHERE ID = "+id+";");
			
		  setRs4(stmt.executeUpdate());
		  
		  stmt.close();
		  
	}
	public void PesquisaEmprestimo(String cpf) throws SQLException{
		 PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("Select*from tb_emprestimo WHERE CPF = '"+cpf+"';");
		  
		  ResultSet rs = stmt.executeQuery();
		   
		  while(rs.next()){
			  System.out.println("\nEmprestimo de numero : "+rs.getInt("ID"));
			  System.out.println("Valor do Emprestimo : "+rs.getDouble("VALORPARCELAS"));
			  System.out.println("Numero de parcelas que faltam pagar : "+rs.getInt("PARCELAS")+"\n");  
		  }
		  rs.close();
		  stmt.close();
	}
	public double PesquisarValorParcela(int id) throws SQLException{
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("select tb_emprestimos from tb_emprestimo where ID = "+id+";");
		
		  ResultSet rs = stmt.executeQuery();
		  
		  rs.next();
		   
		  double valor = rs.getInt("VALORPARCELAS");
		
		  rs.close();
		  stmt.close();
			
		  return valor;
	}
	public void PagarEmprestimo(String cpf,int id,int numero,double saldo) throws SQLException{
		Pesquisa p = new Pesquisa();
		
		double valor = p.PesquisarValorParcela(id);
		
		 PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("UPDATE tb_conta SET  SALDO = SALDO -"+valor*numero+" WHERE CPF = '"+cpf+"';");
			
		  setRs5(stmt.executeUpdate());
		  
		  stmt.close();		
		  
		  Extrato extrato = new Extrato();
		  
		  extrato.adicionamovimetacao(cpf,"Pagamento Extrato",valor*numero,0,saldo);
		  
		
	}

	public void DeletarEmprestimo() throws SQLException {
		PreparedStatement stmt = (PreparedStatement) this.connection.prepareStatement("delete from tb_emprestimo  WHERE PARCELAS = 0;");
		
		  setRs6(stmt.executeUpdate());
		  
		  stmt.close();	
		
	}

	public int getRs() {
		return rs;
	}

	public void setRs(int rs) {
		this.rs = rs;
	}

	public int getRs1() {
		return rs1;
	}

	public void setRs1(int rs1) {
		this.rs1 = rs1;
	}

	public int getRs2() {
		return rs2;
	}

	public void setRs2(int rs2) {
		this.rs2 = rs2;
	}

	public int getRs3() {
		return rs3;
	}

	public void setRs3(int rs3) {
		this.rs3 = rs3;
	}

	public int getRs4() {
		return rs4;
	}

	public void setRs4(int rs4) {
		this.rs4 = rs4;
	}

	public int getRs5() {
		return rs5;
	}

	public void setRs5(int rs5) {
		this.rs5 = rs5;
	}

	public int getRs6() {
		return rs6;
	}

	public void setRs6(int rs6) {
		this.rs6 = rs6;
	}
}
